package com.webframe.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.core.metadata.TableMetaDataContext;
import org.springframework.jdbc.core.metadata.TableMetaDataProvider;
import org.springframework.jdbc.core.metadata.TableMetaDataProviderFactory;
import org.springframework.jdbc.core.metadata.TableParameterMetaData;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.webframe.constant.WFConstant;
import com.webframe.util.UUIDGenerator;

/**
 * 类功能描述：
 * 
 */
@Repository
public class JdbcDao extends JdbcDaoSupport implements IJdbcDao
{
	@Autowired
	public void setParentDataSource(DataSource dataSource)
	{
		super.setDataSource(dataSource);
	}
	/*
	 * (non-Javadoc)
	 * 
	 * @see com.berheley.bi.dao.JdbcBaseDao#doBatchInsert(java.lang.String[])
	 */
	@Override
	public void doBatchInsert(String[] sqls) throws Exception
	{
		getJdbcTemplate().batchUpdate(sqls);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.berheley.bi.dao.JdbcBaseDao#doDeleteBySql(java.lang.String)
	 */
	@Override
	public void doDeleteBySql(String sql)
	{
		getJdbcTemplate().execute(sql);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.berheley.bi.dao.JdbcBaseDao#doDeleteExecuteByID(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	public void doDeleteExecuteByID(String tableName, String pkColumnName,
			String ids)
	{
		getJdbcTemplate().execute(
				"DELETE FROM " + tableName + " where " + pkColumnName + " in ("
						+ getIds(ids) + ")");

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.berheley.bi.dao.JdbcBaseDao#doDeleteExecuteByID(java.lang.String,
	 * java.lang.String)
	 */
	@Override
	public void doDeleteExecuteByID(String tableName, String ids)
	{
		getJdbcTemplate().execute("DELETE FROM " + tableName + " where ID_ in (" + getIds(ids) + ")");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.berheley.bi.dao.JdbcBaseDao#doInsertExecute(java.lang.String,
	 * java.util.Map)
	 */
	@Override
	public void doInsertExecute(String tableName, Map<String, Object> object)
			throws Exception
	{

		setTableName(tableName);
		if (getTableName() == null)
		{
			throw new InvalidDataAccessApiUsageException(
					"Table name is required");
		}

		try
		{
			getJdbcTemplate().afterPropertiesSet();
		} catch (IllegalArgumentException ex)
		{
			throw new InvalidDataAccessApiUsageException(ex.getMessage());
		}
		tableMetaDataContext.processMetaData(getDataSource(), getColumnNames(), generatedKeyNames);
		opString = createInsertString(tableMetaDataContext.getTableColumns(), object);
		opTypes = createInsertTypes(tableMetaDataContext.getTableColumns(), object);
		List<Object> values = getUsedColumnValue(tableMetaDataContext.getTableColumns(), object);
		getJdbcTemplate().update(opString, values.toArray(), getOpTypes());

	}

	public void doInsertOrUpdate(String tableName, Map<String, Object> object, String pkColumnName) throws Exception
	{
		setTableName(tableName);
		if (getTableName() == null)
		{
			throw new InvalidDataAccessApiUsageException(
					"Table name is required");
		}
		try
		{
			getJdbcTemplate().afterPropertiesSet();
		} catch (IllegalArgumentException ex)
		{
			throw new InvalidDataAccessApiUsageException(ex.getMessage());
		}
		tableMetaDataContext.processMetaData(getDataSource(), getColumnNames(),
				generatedKeyNames);
		if (object.get(pkColumnName) == null
				|| object.get(pkColumnName).equals(""))
		{
			object.put(pkColumnName, UUIDGenerator.getUUID());
			opString = createInsertString(tableMetaDataContext
					.getTableColumns(), object);
		} else
		{
			opString = createUpdateString(tableMetaDataContext
					.getTableColumns(), object, pkColumnName);
		}
		opTypes = createInsertTypes(tableMetaDataContext.getTableColumns(),
				object);
		List<Object> values = getUsedColumnValue(tableMetaDataContext
				.getTableColumns(), object);
		getJdbcTemplate().update(opString, values.toArray(), getOpTypes());

	}

	public void doInsertOrUpdate(Map<String, Object> object) throws Exception
	{
		String pkColumnName= WFConstant.Constant_ID_;
		if(object.get(WFConstant.Constant_PKCOLUMNNAME_)!=null&&!object.get(WFConstant.Constant_PKCOLUMNNAME_).equals(""))
		{
			pkColumnName=object.get(WFConstant.Constant_PKCOLUMNNAME_).toString();
		}
		doInsertOrUpdate(object.get(WFConstant.Constant_TableName_).toString(), object,pkColumnName);
	}

	public void doBatchInsertOrUpdate(String tableName,
				List<Map<String, Object>> objects, String pkColumnName)
				throws Exception
	{
		setTableName(tableName);
		if (getTableName() == null)
		{
			throw new InvalidDataAccessApiUsageException(
					"Table name is required");
		}

		try
		{
			getJdbcTemplate().afterPropertiesSet();
		} catch (IllegalArgumentException ex)
		{
			throw new InvalidDataAccessApiUsageException(ex.getMessage());
		}
		if(objects.size() > 0)
		{
			Map<String, Object> object = objects.get(0);
			tableMetaDataContext.processMetaData(getDataSource(), getColumnNames(), generatedKeyNames);
			if (object.get(pkColumnName) == null
						|| object.get(pkColumnName).equals(""))
			{
				object.put(pkColumnName, UUIDGenerator.getUUID());
				opString = createInsertString(tableMetaDataContext.getTableColumns(), object);
			} else
			{
				opString = createUpdateString(tableMetaDataContext.getTableColumns(), object, pkColumnName);
			}
			opTypes = createInsertTypes(tableMetaDataContext.getTableColumns(), object);
			List<Object[]> valuesArray = new ArrayList<Object[]>();
			for (Map<String, Object> obj : objects)
			{
				List<Object> values = getUsedColumnValue(tableMetaDataContext.getTableColumns(), obj);
				valuesArray.add(values.toArray());
			}
			getJdbcTemplate().batchUpdate(opString, valuesArray, getOpTypes());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.berheley.bi.dao.JdbcBaseDao#doUpdateExecuteByID(java.lang.String,
	 * java.lang.String, java.lang.String, java.util.Map)
	 */
	@Override
	public void doUpdateExecuteByID(String tableName, String pkColumnName,
			String id, Map<String, Object> object) throws SQLException
	{
		object.put(pkColumnName, id);
		setTableName(tableName);
		if (getTableName() == null)
		{
			throw new InvalidDataAccessApiUsageException(
					"Table name is required");
		}

		try
		{
			getJdbcTemplate().afterPropertiesSet();
		} catch (IllegalArgumentException ex)
		{
			throw new InvalidDataAccessApiUsageException(ex.getMessage());
		}
		tableMetaDataContext.processMetaData(getDataSource(), getColumnNames(),
				generatedKeyNames);
		opString = createUpdateString(tableMetaDataContext.getTableColumns(),
				object, pkColumnName);
		opTypes = createInsertTypes(tableMetaDataContext.getTableColumns(),
				object);
		List<Object> values = getUsedColumnValue(tableMetaDataContext
				.getTableColumns(), object);
		getJdbcTemplate().update(opString, values.toArray(), getOpTypes());

	}

	private String createInsertString(final List<String> columns,
			final Map<String, Object> object)
	{

		Iterator<String> params = object.keySet().iterator();
		List<String> usedColumns = new ArrayList<String>();
		String tmp;
		while (params.hasNext())
		{
			tmp = params.next();
			if (columns.contains(tmp))
			{
				usedColumns.add(tmp);
			}
		}

		StringBuilder insertStatement = new StringBuilder();
		insertStatement.append("INSERT INTO ");
		if (tableMetaDataContext.getSchemaName() != null)
		{
			insertStatement.append(tableMetaDataContext.getSchemaName());
			insertStatement.append(".");
		}
		insertStatement.append(tableMetaDataContext.getTableName());
		insertStatement.append(" (");
		int columnCount = 0;
		for (String columnName : usedColumns)
		{
			if (columnCount > 0)
			{
				insertStatement.append(", ");
			}
			insertStatement.append(columnName);
			columnCount++;
		}
		insertStatement.append(") VALUES(");
		if (columnCount < 1)
		{
			throw new InvalidDataAccessApiUsageException(
					"Unable to locate columns for table '"
							+ this.getTableName()
							+ "' so an insert statement can't be generated");
		}
		for (int i = 0; i < columnCount; i++)
		{
			if (i > 0)
			{
				insertStatement.append(", ");
			}
			insertStatement.append("?");
		}
		insertStatement.append(")");
		return insertStatement.toString();

	}

	private String createUpdateString(final List<String> columns,
			final Map<String, Object> object, String pkColumnName)
	{

		Iterator<String> params = object.keySet().iterator();
		List<String> usedColumns = new ArrayList<String>();
		String tmp;
		while (params.hasNext())
		{
			tmp = params.next();
			if (columns.contains(tmp))
			{
				usedColumns.add(tmp);
			}
		}

		StringBuilder insertStatement = new StringBuilder();
		insertStatement.append("UPDATE ");
		if (tableMetaDataContext.getSchemaName() != null)
		{
			insertStatement.append(tableMetaDataContext.getSchemaName());
			insertStatement.append(".");
		}
		insertStatement.append(tableMetaDataContext.getTableName());
		insertStatement.append(" SET ");
		int columnCount = 0;
		for (String columnName : usedColumns)
		{
			if (columnCount > 0)
			{
				insertStatement.append(", ");
			}
			insertStatement.append(columnName + "=?");
			columnCount++;
		}
		insertStatement.append(" WHERE " + pkColumnName + " ='"
				+ object.get(pkColumnName).toString() + "'");
		return insertStatement.toString();

	}

	private int[] createInsertTypes(final List<String> columns,
			final Map<String, Object> object)
	{

		Iterator<String> params = object.keySet().iterator();
		List<String> usedColumns = new ArrayList<String>();
		String tmp;
		while (params.hasNext())
		{
			tmp = params.next();
			if (columns.contains(tmp))
			{
				usedColumns.add(tmp);
			}
		}
		int[] types = new int[usedColumns.size()];
		TableMetaDataProvider metaDataProvider = TableMetaDataProviderFactory
				.createMetaDataProvider(getDataSource(), tableMetaDataContext);
		List<TableParameterMetaData> parameters = metaDataProvider
				.getTableParameterMetaData();
		Map<String, TableParameterMetaData> parameterMap = new HashMap<String, TableParameterMetaData>(
				parameters.size());
		for (TableParameterMetaData tpmd : parameters)
		{
			parameterMap.put(tpmd.getParameterName().toUpperCase(), tpmd);
		}

		int typeIndx = 0;
		for (String column : usedColumns)
		{
			if (column == null)
			{
				types[typeIndx] = SqlTypeValue.TYPE_UNKNOWN;
			} else
			{
				TableParameterMetaData tpmd = parameterMap.get(column
						.toUpperCase());
				if (tpmd != null)
				{
					types[typeIndx] = tpmd.getSqlType();
					if(types[typeIndx]==java.sql.Types.OTHER)
					{
						types[typeIndx]=java.sql.Types.VARCHAR;
					}
				} else
				{
					types[typeIndx] = SqlTypeValue.TYPE_UNKNOWN;
				}
			}
			typeIndx++;
		}

		return types;

	}

	private List<Object> getUsedColumnValue(final List<String> columns,
			Map<String, Object> object)
	{
		List<Object> result = new ArrayList<Object>();
		for (String column : object.keySet())
		{
			//update by songxiaoyan begin    修改原因：数字等非字符串类型在不传值的情况下，给result赋空串报错，修改后如果空值时统一赋值null
			if (columns.contains(column)&&"".equals(object.get(column)))  
				result.add(null);
			else if(columns.contains(column))
				result.add(object.get(column));
			//update by songxiaoyan end
		}
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.berheley.bi.dao.JdbcBaseDao#doQueryByField(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	@Override
	@SuppressWarnings({"rawtypes", "unchecked"})
	public List doQueryByField(String tableName, String fieldName,
			String fieldValue)
	{
		List list = getJdbcTemplate().queryForList(
				"SELECT * FROM " + tableName + " where " + fieldName + "='"
						+ fieldValue + "'");
		if (list == null)
			return new ArrayList();
		else
			return list;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.berheley.bi.dao.JdbcBaseDao#doQueryById(java.lang.String,
	 * java.lang.String)
	 */
	@SuppressWarnings({
				"rawtypes", "unchecked"})
	@Override
	public Map doQueryById(String id, String tableName)
	{
		Map result = new HashMap();
		List list = getJdbcTemplate().queryForList(
				"SELECT * FROM " + tableName + " where ID_='" + id + "'");
		if (list != null && list.size() > 0)
			result = (Map) list.get(0);
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.berheley.bi.dao.JdbcBaseDao#doQueryBySql(java.lang.String)
	 */
	@SuppressWarnings({
				"rawtypes", "unchecked"})
	@Override
	public List doQueryBySql(String sql)
	{
		List list = getJdbcTemplate().queryForList(sql);
		if (list == null)
			return new ArrayList();
		else
			return list;
	}

	@Override
	public List<Map<String, Object>> doQueryBySql(String sql, Object... params)
	{
		List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql, params);
		if (list == null)
			return new ArrayList<Map<String, Object>>();
		else
			return list;
	}

	/*
	 * (non-Javadoc)
	 * 
	 */
	@Override
	public void doUpdateBySql(String sql)
	{
		getJdbcTemplate().update(sql);

	}

	public StringBuffer getIds(String id)
	{
		String[] ids = id.split(",");
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < ids.length; i++)
		{
			sb.append("'");
			sb.append(ids[i]);
			sb.append("'");
			if (i + 1 < ids.length)
				sb.append(",");
		}
		return sb;
	}

	/**
	 * 获得JDBC操作的表名
	 */
	public String getTableName()
	{
		return tableMetaDataContext.getTableName();
	}

	/**
	 * 设置JDBC操作的表名
	 */
	public void setTableName(String tableName)
	{
		tableMetaDataContext.setTableName(tableName);
	}

	/**
	 * 设置JDBC操作的列名
	 */
	public void setColumnNames(List<String> columnNames)
	{
		declaredColumns.clear();
		declaredColumns.addAll(columnNames);
	}

	/**
	 * 获得JDBC操作的列名
	 */
	public List<String> getColumnNames()
	{
		return Collections.unmodifiableList(declaredColumns);
	}

	/**
	 * Specify whether the parameter metadata for the call should be used. The
	 * default is true.
	 */
	public void setAccessTableColumnMetaData(boolean accessTableColumnMetaData)
	{
		this.tableMetaDataContext
				.setAccessTableColumnMetaData(accessTableColumnMetaData);
	}

	/**
	 * Specify whether the default for including synonyms should be changed. The
	 * default is false.
	 */
	public void setOverrideIncludeSynonymsDefault(boolean override)
	{
		this.tableMetaDataContext.setOverrideIncludeSynonymsDefault(override);
	}

	/**
	 * 获得JDBC insert的prepareStatement的SQL语句
	 */
	public String getOpString()
	{
		return opString;
	}

	/**
	 * 获得操作列的类型 {@link java.sql.Types}
	 */
	public int[] getOpTypes()
	{
		return opTypes;
	}

	/** Logger available to subclasses */
	protected final Log logger = LogFactory.getLog(getClass());

	/** List of columns objects to be used in insert statement */
	private List<String> declaredColumns = new ArrayList<String>();

	/** the generated string used for insert statement */
	private String opString;

	/** the SQL Type information for the insert columns */
	private int[] opTypes;

	/** the names of the columns holding the generated key */
	private String[] generatedKeyNames = new String[] {};

	/** context used to retrieve and manage database metadata */
	private TableMetaDataContext tableMetaDataContext = new TableMetaDataContext();

}
